create procedure [dbo].[BAEOrderProductGetAllByCategory] @OrderCategoryID as
int
AS
    (
        SELECT op.OrderProductID, op.Title, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, opcl.IsFeatured, opcl.SortOrder
        FROM OrderProduct op, OrderProductCategoryLookup opcl
        WHERE opcl.OrderProductID = op.OrderProductID AND opcl.OrderCategoryID = @OrderCategoryID AND op.IsSuperProduct = 1
        UNION
        SELECT op.OrderProductID, imisp.TITLE COLLATE database_default, op.IsSuperProduct, imisp.PRODUCT_CODE COLLATE database_default AS ProductCode, WEB_OPTION AS SellOnWeb, opcl.IsFeatured, opcl.SortOrder
        FROM OrderProduct op
        INNER JOIN Product imisp ON op.ProductCode COLLATE database_default = imisp.PRODUCT_CODE COLLATE database_default
        INNER JOIN OrderProductCategoryLookup opcl ON op.OrderProductID = opcl.OrderProductID
        WHERE op.IsSuperProduct = 0 AND imisp.PROD_TYPE = 'SALES' AND opcl.OrderCategoryID = @OrderCategoryID
    )
    ORDER BY opcl.SortOrder;

GO